# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://doc.scrapy.org/en/latest/topics/item-pipeline.html

from scrapy.conf import settings
from amazon.items import CatelogItem,ProductItem
from scrapy.exceptions import DropItem
from amazon.models import db_connect,create_amazon_table,Catelog,Product
from sqlalchemy.orm import sessionmaker
import hashlib
import time
import re

class CatelogPipeline(object):

    def __init__(self, settings):
        self.settings = settings

    @classmethod
    def from_crawler(cls, crawler):
        return cls(crawler.settings)

    def open_spider(self, spider):
        # 连接数据库
        engine = db_connect()
        self.Session = sessionmaker(bind=engine)

    def close_spider(self, spider):
        pass

    def process_item(self, item, spider):
        session = self.Session()
        if 'cate_line' in item and len(item['cate_line']) > 0:
            cateItem = Catelog(
                id = item['_id'],
                cate_top = item['cate_top'],
                cate_name = item['cate_level_name'],
                cate_level = item['cate_level'],
                cate_line = item['cate_line']
            )
            if 'bestsellers_url' in item and item['bestsellers_url'] is not None:
                cateItem.bestsellers_url = item['bestsellers_url']
            if 'newreleases_url' in item and item['newreleases_url'] is not None:
                cateItem.newreleases_url = item['newreleases_url']
            if 'movers_url' in item and item['movers_url'] is not None:
                cateItem.movers_url = item['movers_url']
            session.merge(cateItem)
            session.commit()
        else:
            DropItem("item lack price and url %s"%item)
        session.close()
        return item
    
class ProductPipeline(object):

    def __init__(self, settings):
        self.settings = settings

    @classmethod
    def from_crawler(cls, crawler):
        return cls(crawler.settings)

    def open_spider(self, spider):
        # 连接数据库
        engine = db_connect()
        self.Session = sessionmaker(bind=engine)

    def close_spider(self, spider):
        pass

    def process_item(self, item, spider):
        session = self.Session()
        if 'product_url' in item and self.ourPrice(item) > 0:
            productId = self.productId(item)
            spider.log(productId)
            spider.log(item)
            productInfo = Product(
                id=productId,
                product_url=item['product_url']
            )
            if 'product_img' in item and item['product_img'] is not None:
                productInfo.product_img = item['product_img']
            if 'product_name' in item and item['product_name'] is not None:
                productInfo.product_name = item['product_name']
            if 'product_rating' in item and item['product_rating'] is not None:
                productInfo.product_rating = self.productRating(item)
            if 'product_reviews' in item and item['product_reviews'] is not None:
                productInfo.product_reviews = self.productReviews(item)
            if 'product_price' in item and item['product_price'] is not None:
                productInfo.product_price = self.ourPrice(item)
            if 'price_amazon' in item and item['price_amazon'] is not None:
                productInfo.price_amazon = item["price_amazon"]
            if 'month_sales' in item and item['month_sales'] is not None:
                productInfo.month_sales = self.monthSales(item)
            if 'rank_top' in item and item['rank_top'] is not None:
                productInfo.rank_top = item['rank_top']
            
            productInfoDb = session.query(Product).filter(Product.id == productId).first()
            productInDb = {}
            if productInfoDb is None:
                pass
            else:
                productInDb = productInfoDb.to_dict()
            
            if 'cate_ref' in item and item['cate_ref'] is not None:
                productInfo.cate_ref = self.combineProductCatelog('cate_ref',item,productInDb)
            if 'cate_top' in item and item['cate_top'] is not None:
                # 处理 cate_top
                productInfo.cate_top = item['cate_top']
            if 'bestsellers_rank' in item and item['bestsellers_rank'] is not None:
                # 处理 bestsellers_rank
                productInfo.bestsellers_rank = self.combineProductRank('bestsellers_rank',item,productInDb)
            if 'newreleases_rank' in item and item['newreleases_rank'] is not None:    
                # 处理 newreleases_rank
                productInfo.newreleases_rank = self.combineProductRank('newreleases_rank',item,productInDb)
            if 'movers_rank' in item and item['movers_rank'] is not None:    
                # 处理 movers_rank
                productInfo.movers_rank = self.combineProductRank('movers_rank',item,productInDb)
            session.merge(productInfo)
            session.commit()
        else:
            DropItem("item lack price and url %s"%item)
        session.close()
        return item
    
    def productId(self,product):
        sha1 = hashlib.sha1()
        sha1.update(product['product_url'].encode('utf-8'))
        return sha1.hexdigest()

    def ourPrice(self,product):
        price = 0
        if 'price_amazon' in product and product['price_amazon'] is not None:
            price = product['price_amazon'].split('-')[0].strip()
            if price.find('$') > -1:
                price = price.replace('$',"")
                price = price.replace(',',"")
                price = round(float(price),2)
        return price

    def productRating(self,product):
        rating = 0.0
        if 'product_rating' in product and product['product_rating'] is not None:
            rating = round(float(product['product_rating'].split('out')[0].strip()),1)
        return rating
    
    def productReviews(self,product):
        reviews = 0
        if 'product_reviews' in product and product['product_reviews'] is not None:
            reviews = int(re.sub(r'\D',"",product['product_reviews']))
        return reviews

    def monthSales(self,product):
        sales = 0
        if 'month_sales' in product and product['month_sales'] is not None:
            sales = re.sub(r'\D',"",product['month_sales'])
            if sales != "":
                sales = int(sales)
        return sales
    
    def combineProductRank(self, rankName, productItem, productInDb):
        productRankItem = {}
        if rankName in productItem and productItem[rankName] is not None:
            productRankItem = productItem[rankName]
        if rankName in productInDb and productInDb[rankName] is not None:
            for rankKey in productInDb[rankName].keys():
                if rankKey not in productRankItem.keys():
                    productRankItem[rankKey] = productInDb[rankName][rankKey]
        return productRankItem
    
    def combineProductCatelog(self,cateKey,productItem, productInDb):
        productCateItem = []
        if cateKey in productItem and productItem[cateKey] is not None:
            productCateItem = productCateItem + productItem[cateKey]
        if cateKey in productInDb and productInDb[cateKey] is not None:
            for cate in productInDb[cateKey]:
                if cate not in productCateItem:
                    productCateItem.append(cate)
        return productCateItem

